﻿using MySql.Data.MySqlClient;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Reflection;

namespace bpms.Dao
{
    public class MySqlDbHelper
    {
        private static string _connectionString;

        // 从配置初始化（在Program.cs中配置）
        public static void Configure(string connectionString)
        {
            _connectionString = connectionString;
        }

        // 通用泛型查询方法
        public static List<T> QueryList<T>(string sql, params MySqlParameter[] parameters) where T : new()
        {
            using (var conn = new MySqlConnection(_connectionString))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(parameters);
                    using (var reader = cmd.ExecuteReader())
                    {
                        return MapToList<T>(reader);
                    }
                }
            }
        }

        // 单个实体查询
        public static T Query<T>(string sql, params MySqlParameter[] parameters) where T : new()
        {
            using (var conn = new MySqlConnection(_connectionString))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(parameters);
                    using (var reader = cmd.ExecuteReader())
                    {
                        return MapToSingle<T>(reader);
                    }
                }
            }
        }

        // 增删改通用方法
        public static int Execute(string sql, params MySqlParameter[] parameters)
        {
            using (var conn = new MySqlConnection(_connectionString))
            {
                conn.Open();
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        // 实体映射核心方法
        private static List<T> MapToList<T>(MySqlDataReader reader) where T : new()
        {
            var list = new List<T>();
            var props = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);

            // 构建属性字典（Key为数据库列名）
            var propDict = new Dictionary<string, PropertyInfo>();
            foreach (var prop in props)
            {
                var attr = prop.GetCustomAttribute<ColumnAttribute>();
                var colName = attr?.Name ?? prop.Name; // 优先使用特性中的列名
                propDict[colName.ToLower()] = prop;
            }

            while (reader.Read())
            {
                var obj = new T();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var dbColName = reader.GetName(i).ToLower();
                    if (propDict.TryGetValue(dbColName, out var prop))
                    {
                        var value = reader[i];
                        if (value == DBNull.Value) continue;

                        // 添加枚举类型处理
                        if (prop.PropertyType.IsEnum)
                        {
                            prop.SetValue(obj, Enum.ToObject(prop.PropertyType, value));
                        }
                        else
                        {
                            var targetType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                            prop.SetValue(obj, Convert.ChangeType(value, targetType));
                        }
                    }
                }
                list.Add(obj);
            }
            return list;
        }

        private static T MapToSingle<T>(MySqlDataReader reader) where T : new()
        {
            // 如果没有数据返回默认值
            if (!reader.Read()) return default;

            var obj = new T();
            var props = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);

            // 构建属性字典（Key为数据库列名）
            var propDict = new Dictionary<string, PropertyInfo>();
            foreach (var prop in props)
            {
                var attr = prop.GetCustomAttribute<ColumnAttribute>();
                var colName = (attr?.Name ?? prop.Name).ToLower(); // 统一转小写
                propDict[colName] = prop;
            }

            for (int i = 0; i < reader.FieldCount; i++)
            {
                var dbColName = reader.GetName(i).ToLower();
                if (propDict.TryGetValue(dbColName, out var prop))
                {
                    var value = reader[i];
                    if (value == DBNull.Value) continue;

                    // 添加枚举类型处理
                    if (prop.PropertyType.IsEnum)
                    {
                        prop.SetValue(obj, Enum.ToObject(prop.PropertyType, value));
                    }
                    else
                    {
                        var targetType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                        prop.SetValue(obj, Convert.ChangeType(value, targetType));
                    }
                }
            }
            return obj;
        }
    }
}
